-- =============================================
-- This stored procedure is used to content folder to a specified location.
-- InTargetNavMenuID represents a new parent folder for InMoveNavMenuID.
-- Content folders are ordered alphabetically, so the sp figures out the
-- correct position and calls amsp_CMMoveNavMenu
--
-- Modifications
-- 09/10/2003    E.Tatsui
-- =============================================

CREATE PROCEDURE amsp_CMMoveContentFolder
  @InMoveNavMenuID numeric,
  @InTargetNavMenuID numeric,
  @OutErrorMessage varchar(255) = NULL OUTPUT
AS
BEGIN
  DECLARE
    @Title varchar(255),
    @BeforeID numeric,
    @ChildNum integer,
    @TargetCategoryDepth integer

  SELECT @TargetCategoryDepth = CategoryDepth
    FROM Nav_Menu
   WHERE NavMenuID = @InTargetNavMenuID

  -- If the target is the root of content folder, get all the level 1s.
  IF @TargetCategoryDepth = 0
    SELECT @ChildNum = Count(*)
      FROM Nav_Menu
     WHERE CategoryDepth = 1
       AND NavContentGroupInd = 'C'
  ELSE
    SELECT @ChildNum = Count(*)
      FROM Nav_Menu
     WHERE IsNull(ParentNavMenuID,0)  = IsNull(@InTargetNavMenuID,0)

  IF @ChildNum = 0
    EXEC amsp_CMMoveNavMenu @InMoveNavMenuID, @InTargetNavMenuID, 'M', 'Lower', @OutErrorMessage OUTPUT
  ELSE BEGIN
    SELECT @Title = Title
      FROM Nav_Menu
     WHERE NavMenuID = @InMoveNavMenuID
    
    -- Get the item that is going to be right before this item.
    IF @TargetCategoryDepth = 0
      SELECT TOP 1 @BeforeID = NavMenuID
        FROM Nav_Menu
       WHERE CategoryDepth = 1
         AND NavContentGroupInd = 'C'
         AND Title < @Title
       ORDER BY Title DESC
    ELSE
      SELECT TOP 1 @BeforeID = NavMenuID
        FROM Nav_Menu
       WHERE IsNull(ParentNavMenuID,0) = IsNull(@InTargetNavMenuID,0)
         AND Title < @Title
       ORDER BY Title DESC

    IF @BeforeID IS NOT NULL
      EXEC amsp_CMMoveNavMenu @InMoveNavMenuID, @BeforeID, 'M', 'Same', @OutErrorMessage OUTPUT
    ELSE -- If this item goes to the top, add it right below the parent.
      EXEC amsp_CMMoveNavMenu @InMoveNavMenuID, @InTargetNavMenuID, 'M', 'Lower', @OutErrorMessage OUTPUT
  END

END

GO
GRANT EXECUTE ON  [dbo].[amsp_CMMoveContentFolder] TO [IMIS]
GO